package com.huichenghe.xinlvsh01.DbEntities;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * Created by lixiaoning on 15-11-18.
 */
public class MyDBHelperForDayData extends SQLiteDBOnSdcardHelper {

    static MyDBHelperForDayData instance = null;
    public static String DATABASE_NAME = "xlsh_simple_db.db";       // 数据库名字，继承自己实现的保存在sd卡的帮助类

    public static MyDBHelperForDayData getInstance(Context mContext) {
        if (instance == null) {
            instance = new MyDBHelperForDayData(mContext);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 全天总数据表
        String sql = " CREATE TABLE if not exists day_data_one (_id INTEGER PRIMARY KEY AUTOINCREMENT, "        // 建表四个字段，
                + "userAccount TEXT , day TEXT , stepAll Integer , calorie Integer ,"
                + "mileage Integer , movementTime TEXT, moveCalorie INTEGER, sitTime TEXT, sitCalorie INTEGER); ";
        db.execSQL(sql);

        // 心率数据表
        String sqlOne = "CREATE TABLE IF NOT EXISTS heart_reat_table (_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "userAccount TEXT, day TEXT, one TEXT, two TEXT, three TEXT, four TEXT, five TEXT, six TEXT,"
                + "seven TEXT, eight TEXT, nine TEXT, ten TEXT, one1 TEXT, two1 TEXT, three1 TEXT, four1 TEXT,"
                + "five1 TEXT, six1 TEXT, seven1 TEXT, eight1 TEXT, nine1 TEXT, ten1 TEXT, one2 TEXT, two2 TEXT,"
                + "three2 TEXT, four2 TEXT);";
        db.execSQL(sqlOne);


        // 离线数据表
        String sqlOutline = "CREATE TABLE IF NOT EXISTS outline_movement_table (_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "userAccount TEXT, day TEXT, type TEXT, startTime TEXT, endTime TEXT, calorie INTEGER, stepTotle INTEGER,"
                + "heartRate INTEGER);";
        db.execSQL(sqlOutline);


        // 每小时计步数据表
        String sqlEachHourData = "CREATE TABLE IF NOT EXISTS each_hour_data_table (_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "userAccount TEXT, day TEXT, one INTEGER, two INTEGER, three INTEGER, four INTEGER, five INTEGER, six INTEGER, seven INTEGER,"
                + "eight INTEGER, nine INTEGER, ten INTEGER, one1 INTEGER, two1 INTEGER, three1 INTEGER, four1 INTEGER, five1 INTEGER, six1 INTEGER,"
                + "seven1 INTEGER, eight1 INTEGER, nine1 INTEGER, ten1 INTEGER, one2 INTEGER, two2 INTEGER, three2 INTEGER, four2 INTEGER);";
        db.execSQL(sqlEachHourData);

        // 每小时卡路里数据表
        String sqlEachHourCalorieData = "CREATE TABLE IF NOT EXISTS each_hour_calorie_data_table (_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "userAccount TEXT, day TEXT, one INTEGER, two INTEGER, three INTEGER, four INTEGER, five INTEGER, six INTEGER, seven INTEGER,"
                + "eight INTEGER, nine INTEGER, ten INTEGER, one1 INTEGER, two1 INTEGER, three1 INTEGER, four1 INTEGER, five1 INTEGER, six1 INTEGER,"
                + "seven1 INTEGER, eight1 INTEGER, nine1 INTEGER, ten1 INTEGER, one2 INTEGER, two2 INTEGER, three2 INTEGER, four2 INTEGER);";
        db.execSQL(sqlEachHourCalorieData);

        // 睡眠数据表
        String sqlSleepData = "CREATE TABLE IF NOT EXISTS sleep_table (_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "userAccount TEXT, day TEXT, sleepData TEXT);";
        db.execSQL(sqlSleepData);
    }


    public Cursor selectTheDay(Context mContext, String userAccount)
    {
        SQLiteDatabase db = getInstance(mContext).getMyReadableDatabase();
        String sql = "SELECT day FROM each_hour_data_table WHERE userAccount = ?;";
        Cursor mCursor = db.rawQuery(sql, new String[]{userAccount});
        return mCursor;
    }






    public Cursor selectTheSleepData(Context mContext, String userAccount, String day)
    {
        SQLiteDatabase db = getInstance(mContext).getMyReadableDatabase();
        String sqlForSelect = "SELECT sleepData FROM sleep_table WHERE userAccount = ? and day = ?;";
        Cursor mCursor = db.rawQuery(sqlForSelect, new String[]{userAccount, day});
        return mCursor;
    }


    /**
     * 更新睡眠数据
     * @param mContext
     * @param userAccount
     * @param day
     * @param dataSleep
     */
    public void updateTheSleepData(Context mContext, String userAccount, String day, String dataSleep)
    {
        SQLiteDatabase db = getInstance(mContext).getMyWritableDatabase();
        String sqlForUpdate = "UPDATE sleep_table SET sleepData = ? WHERE userAccount = ? and day = ?;";
        db.execSQL(sqlForUpdate, new String[]{dataSleep, userAccount, day});
    }



    /**
     * 插入睡眠数据
     * @param mContext
     * @param userAccount
     * @param day
     * @param dataSleep
     */
    public void insertTheSleepData(Context mContext, String userAccount, String day, String dataSleep)
    {
        SQLiteDatabase db = getInstance(mContext).getMyReadableDatabase();
        String sql = "INSERT INTO sleep_table(userAccount, day, sleepData)values(?, ?, ?)";
        db.execSQL(sql, new String[]{userAccount, day, dataSleep});
    }

    /**
     * 插入分时热量数据
     */
    public void insertEachHourCalorieData(Context mContext, String userAccount, String day, int one, int two, int three,
                                   int four, int five, int six, int seven, int eight, int nine, int ten,
                                   int one1, int two1, int three1, int four1, int five1, int six1, int seven1,
                                   int eight1, int nine1, int ten1, int one2, int two2, int three2, int four2)
    {
        SQLiteDatabase db = getInstance(mContext).getMyWritableDatabase();
        db.execSQL("insert into each_hour_calorie_data_table(userAccount, day, one, two, three, four, five, six, seven, eight, nine,"
                        + "ten, one1, two1, three1, four1, five1, six1, seven1, eight1, nine1, ten1, one2, two2, three2, four2)"
                        + "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                new Object[]{userAccount, day, one, two, three, four, five, six, seven, eight, nine, ten, one1, two1,
                        three1, four1, five1, six1, seven1, eight1, nine1, ten1, one2, two2, three2});
    }


    // 查询分时热量数据
    public Cursor getEachHourCalorieData(Context mContext, String userAccount, String day)
    {
        SQLiteDatabase db = getInstance(mContext).getMyReadableDatabase();
        String sqlSelect = "select one, two, three, four, five, six, seven, eight, nine, ten, one1, two1, three1, four1, five1, six1,"
                + "seven1, eight1, nine1, ten1, one2, two2, three2, four2 from each_hour_calorie_data_table where userAccount = ?"
                + "and day = ?;";
        Cursor c = db.rawQuery(sqlSelect, new String[]{userAccount, day});
        return c;
    }


    // 更新热量数据
    public void updateEachHourCalorieData(Context mContext, String userAccount, String day, int one, int two, int three,
                                          int four, int five, int six, int seven, int eight, int nine, int ten,
                                          int one1, int two1, int three1, int four1, int five1, int six1, int seven1,
                                          int eight1, int nine1, int ten1, int one2, int two2, int three2, int four2)
    {
        SQLiteDatabase db = getInstance(mContext).getMyWritableDatabase();
        String spl = "UPDATE each_hour_calorie_data_table SET one = ?, two = ?, three = ?, four = ?, five = ?, six = ?, seven = ?, "
                + "eight = ?, nine = ?, ten = ?, one1 = ?, two1 = ?, three1 = ?, four1 = ?, five1 = ?, six1 = ?, seven1 = ?, "
                + "eight1 = ?, nine1 = ?, ten1 = ?, one2 = ?, two2 = ?, three2 = ?, four2 = ? WHERE userAccount = ? and day = ?;";
        db.execSQL(spl, new Object[]{one, two, three, four, five, six, seven, eight, nine, ten, one1, two1, three1, four1,
                five1, six1, seven1, eight1, nine1, ten1, one2, two2, three2, four2});


    }

    // 插入分时计步数据到数据库

    public void insertEachHourData(Context mContext, String userAccount, String day, int one, int two, int three,
                                   int four, int five, int six, int seven, int eight, int nine, int ten,
                                   int one1, int two1, int three1, int four1, int five1, int six1, int seven1,
                                   int eight1, int nine1, int ten1, int one2, int two2, int three2, int four2)
    {
        SQLiteDatabase db = getInstance(mContext).getMyWritableDatabase();
        db.execSQL("insert into each_hour_data_table(userAccount, day, one, two, three, four, five, six, seven, eight, nine, "
                + "ten, one1, two1, three1, four1, five1, six1, seven1, eight1, nine1, ten1, one2, two2, three2, four2)"
                + "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                new Object[]{userAccount, day, one, two, three, four, five, six, seven, eight, nine, ten, one1, two1,
                three1, four1, five1, six1, seven1, eight1, nine1, ten1, one2, two2, three2});
    }


    // 更新分时计步数据
    public void updateEachHourData(Context mContext, String userAccount, String day, int one, int two, int three,
                                   int four, int five, int six, int seven, int eight, int nine, int ten,
                                   int one1, int two1, int three1, int four1, int five1, int six1, int seven1,
                                   int eight1, int nine1, int ten1, int one2, int two2, int three2, int four2)
    {
        SQLiteDatabase db = getInstance(mContext).getMyWritableDatabase();
        String spl = "UPDATE each_hour_data_table SET one = ?, two = ?, three = ?, four = ?, five = ?, six = ?, seven = ?, "
                + "eight = ?, nine = ?, ten = ?, one1 = ?, two1 = ?, three1 = ?, four1 = ?, five1 = ?, six1 = ?, seven1 = ?, "
                + "eight1 = ?, nine1 = ?, ten1 = ?, one2 = ?, two2 = ?, three2 = ?, four2 = ? WHERE userAccount = ? and day = ?;";
        db.execSQL(spl, new Object[]{one, two, three, four, five, six, seven, eight, nine, ten, one1, two1, three1, four1,
                                     five1, six1, seven1, eight1, nine1, ten1, one2, two2, three2, four2});



    }




    // 查询分时计步数据
    public Cursor getEachHourData(Context mContext, String userAccount, String day)
    {
        SQLiteDatabase db = getInstance(mContext).getMyReadableDatabase();
        String sqlSelect = "select one, two, three, four, five, six, seven, eight, nine, ten, one1, two1, three1, four1, five1, six1,"
                + "seven1, eight1, nine1, ten1, one2, two2, three2, four2 from each_hour_data_table where userAccount = ?"
                + "and day = ?;";
        Cursor c = db.rawQuery(sqlSelect, new String[]{userAccount, day});
        return c;
    }





    /**
     * 插入离线数据到数据库
     * @param mContext
     * @param userAccount
     * @param day
     * @param startTime
     * @param endTime
     * @param calorie
     * @param stepTotle
     */
    public void insertOutLineData(Context mContext, String userAccount, String type, String day,
                                  String startTime, String endTime, int calorie, int stepTotle)
    {
        SQLiteDatabase db = getInstance(mContext).getMyWritableDatabase();
        db.execSQL("insert into outline_movement_table(userAccount, type, day, startTime, endTime, calorie, stepTotle)"
                +"values(?, ?, ?, ?, ?, ?, ?)",
        new Object[]{userAccount, type, day, startTime, endTime, calorie, stepTotle});
        Log.i(TAG, "插入离线数据");
}


    public Cursor selectOutLineData(Context mContext, String userAccount, String day)
    {
        SQLiteDatabase db = getInstance(mContext).getMyReadableDatabase();
        Cursor mCursor = db.rawQuery("select startTime, endTime, calorie, stepTotle, type, heartRate from outline_movement_table"
                        + " where userAccount = ? and day = ?;",
                new String[]{userAccount, day});
        return mCursor;

    }





    private MyDBHelperForDayData(Context mContext) {
        super(mContext, DATABASE_NAME, null, 1);    // 创建数据库
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }


    /**
     * 插入账号日期
     * @param mcContext
     * @param userAccount
     * @param day
     */
    public void insertHrAccount(Context mcContext, String userAccount, String day)
    {
        SQLiteDatabase db = getInstance(mcContext).getMyWritableDatabase();
        String sql = "INSERT INTO heart_reat_table(userAccount, day)values(?, ?)";
        db.execSQL(sql, new String[]{userAccount, day});
    }


    public Cursor selectHrAccount(Context mContext, String userAccount, String day)
    {
        SQLiteDatabase db = getInstance(mContext).getMyReadableDatabase();
        String sql = "SELECT * FROM heart_reat_table WHERE userAccount = ? and day = ?";
        Cursor mCursor = db.rawQuery(sql, new String[]{userAccount, day});
        return mCursor;
    }



    /**
     * 对应某个字段插入心率数据
     * @param mContext
     * @param userAccount
     * @param day
     * @param one
     * @param hr
     */
    public void insertHr(Context mContext, String userAccount, String day, String one, String hr)
    {
        SQLiteDatabase db = getInstance(mContext).getMyWritableDatabase();
        db.execSQL("insert into heart_reat_table(userAccount, day, " + one + ")"
                + "values(?, ?, ?)",
                new Object[]{userAccount, day, hr});
        Log.i(TAG, "插入心率数据" + one);
    }


    /**
     * 查询指定column的数据
     * @param mContext
     * @param userAccount
     * @param day
     * @param column
     */
    public void selecteHr(Context mContext, String userAccount, String day, String column)
    {
        SQLiteDatabase db = getInstance(mContext).getMyReadableDatabase();
        db.rawQuery("SELECT" + column + "FROM heart_reat_table WHERE userAccount = ? AND day = ?;",
                new String[]{userAccount, day});

        Log.i(TAG, "查询" + column + "列");
    }


    /**
     * 更新指定列的数据
     * @param mContext
     * @param userAccount
     * @param day
     * @param column
     * @param data
     */
    public void updateHr(Context mContext, String userAccount, String day, String column, String data)
    {
        SQLiteDatabase db = getInstance(mContext).getMyWritableDatabase();
//        db.execSQL("update heart_reat_table set stepAll = ?, calorie = ?"
//                        + ", mileage = ?, movementTime = ?, moveCalorie = ?"
//                        + ", sitTime = ?, sitCalorie = ? where userAccount = ? and day = ?;",
//                new Object[]{stepAlls, calorie, mileage, movementTime, moveCalorie, sitTime, sitCalorie, userAccount, day});






        db.execSQL("UPDATE heart_reat_table SET " + column + " = ? WHERE userAccount = ? AND day = ?;",
                    new String[]{data, userAccount, day});
        Log.i(TAG, "更新心率数据：" + column + "列");




    }



    /**
     * 插入全天数据，
     * @param context
     * @param userAccount
     * @param day
     * @param stepAll
     * @param calorie
     * @param mileage
     * @param movementTime
     * @param sitTime
     */
    public void insert(final Context context, final String userAccount, final String day, final int stepAll,
                              final int calorie, final int mileage, final String movementTime, final int moveCalorie,
                              final String sitTime,final int sitCalorie) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                SQLiteDatabase db = getInstance(context).getMyWritableDatabase();                                                    // 拿到可写的数据库

                db.execSQL(
                        "insert into day_data_one(userAccount, day, stepAll, calorie, mileage, movementTime, moveCalorie,"
                                +" sitTime, sitCalorie)"
                                +"values(?, ?, ?, ?, ?, ?, ?, ?, ?)",
                        new Object[]{userAccount, day, stepAll, calorie, mileage, movementTime, moveCalorie, sitTime, sitCalorie});
            }
        }).start();

        Log.i(TAG, "插入全天数据");

    }


    /**
     * 查询全天数据
     * @param mContext
     * @param account
     * @param day
     * @return
     */
    public Cursor selecteDayData(Context mContext, String account, String day)
    {

        SQLiteDatabase  db = getInstance(mContext).getMyReadableDatabase();
        Cursor mCursor = db.rawQuery("select stepAll, calorie, mileage, movementTime, moveCalorie, sitTime, sitCalorie"
                        +" from day_data_one where userAccount = ? and day = ?;",
                new String[]{account , day});




        Log.i(TAG, "查询全天数据selecteDayData : account:" + account + "day:" + day);
        return mCursor;
    }


    /**
     * 更新当天全天数据
     * @param context
     * @param userAccount
     * @param day
     * @param stepAlls
     * @param calorie
     * @param mileage
     * @param movementTime
     * @param sitTime
     *String sql = " CREATE TABLE if not exists day_data_one (_id INTEGER PRIMARY KEY AUTOINCREMENT, "        // 建表四个字段，
    + "userAccount TEXT , day TEXT , stepAll Integer , calorie Integer ,"
    + "mileage Integer , movementTime TEXT, moveCalorie INTEGER, sitTime TEXT, sitCalorie INTEGER); ";
     *
     * "insert into day_data_one(userAccount, day, stepAll, calorie, mileage, movementTime, moveCalorie,"
    +" sitTime, sitCalorie)"
    +"values(?, ?, ?, ?, ?, ?, ?, ?, ?)",
     */
    public void updateDayDataToday(Context context, String userAccount, String day, int stepAlls,
                                   int calorie, int mileage, String movementTime, int moveCalorie,
                                   String sitTime, int sitCalorie)
    {
        SQLiteDatabase db = getInstance(context).getMyWritableDatabase();
        // 此处不能用and连接，经多次测试必须用，隔开
        db.execSQL("update day_data_one set stepAll = ?, calorie = ?"
                + ", mileage = ?, movementTime = ?, moveCalorie = ?"
                + ", sitTime = ?, sitCalorie = ? where userAccount = ? and day = ?;",
                new Object[]{stepAlls, calorie, mileage, movementTime, moveCalorie, sitTime, sitCalorie, userAccount, day});
        Log.i(TAG, "更新全天数据" + stepAlls);
        Log.i(TAG, "更新的新数据：" + stepAlls + "--" + calorie + "--" + mileage + "--" + movementTime + "--"
                + moveCalorie + "--" + sitTime + "--" + sitCalorie);
    }


}